import * as fs from 'fs';
import * as path from 'path';
import * as XLSX from 'xlsx';
import { createObjectCsvWriter as createCsvWriter } from 'csv-writer';

/**
 * Excel和CSV文件处理辅助类，用于实现数据的导入导出功能
 */
export class ExcelCsvHelper {
  /**
   * 导出数据到Excel文件
   * params: { siteName?: string, inputs?: object, results?: object, exportType?: 'all' | 'inputs' | 'results' }
   */
  static async exportToExcel(params: any): Promise<any> {
    try {
      const { 
        siteName = '计算数据', 
        inputs = {}, 
        results = {}, 
        exportType = 'all' 
      } = params;

      const dateStr = new Date().toISOString().replace(/[:.]/g, '-');
      
      // 创建工作簿
      const workbook = XLSX.utils.book_new();
      
      // 导出输入参数
      if (exportType === 'all' || exportType === 'inputs') {
        const inputData = this.prepareInputDataForExcel(inputs);
        // 通过设置header选项来控制表头的生成，避免双重表头
        const inputSheet = XLSX.utils.json_to_sheet(inputData, { header: ["参数", "数值", "单位"] });
        XLSX.utils.book_append_sheet(workbook, inputSheet, '输入参数');
      }
      
      // 导出计算结果
      if (exportType === 'all' || exportType === 'results') {
        const resultData = this.prepareResultDataForExcel(results);
        // 通过设置header选项来控制表头的生成，避免双重表头
        const resultSheet = XLSX.utils.json_to_sheet(resultData, { header: ["项目", "数值", "单位"] });
        XLSX.utils.book_append_sheet(workbook, resultSheet, '计算结果');
      }
      
      // 确保reports目录存在，使用与docxHelper.ts相同的路径处理方式
      let reportsDir = path.join(process.cwd(), 'reports');
      let useUserDataDir = false;
      
      try {
        if (!fs.existsSync(reportsDir)) {
          fs.mkdirSync(reportsDir, { recursive: true });
        }
        // 检查目录是否可写入
        fs.accessSync(reportsDir, fs.constants.W_OK);
      } catch (mkdirError: any) {
        // 如果创建目录失败或目录不可写，尝试使用用户数据目录
        console.warn('在当前目录创建reports文件夹失败，尝试使用用户数据目录:', mkdirError.message);
        try {
          const userDataPath = (process as any).userDataPath || require('electron').app.getPath('userData');
          const userReportsDir = path.join(userDataPath, 'reports');
          if (!fs.existsSync(userReportsDir)) {
            fs.mkdirSync(userReportsDir, { recursive: true });
          }
          // 检查用户目录是否可写入
          fs.accessSync(userReportsDir, fs.constants.W_OK);
          // 更新reportsDir为用户数据目录
          reportsDir = userReportsDir;
          useUserDataDir = true;
        } catch (userDirError: any) {
          console.error('无法在用户数据目录创建reports文件夹:', userDirError.message);
          throw new Error('导出文件失败，请检查文件权限或稍后再试');
        }
      }
      
      // 生成文件名和文件路径
      const filename = `${siteName.replace(/[^a-z0-9_\u4e00-\u9fa5]/gi, '_')}_${dateStr}.xlsx`;
      const filepath = path.join(reportsDir, filename);

      console.log('Reports directory path:', reportsDir);
      console.log('Attempting to save Excel file to:', filepath);

      // 使用writeFileSync写入Excel文件
      const workbookBinary = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
      
      try {
        // 检查文件是否已存在且可写
        if (fs.existsSync(filepath)) {
          fs.accessSync(filepath, fs.constants.W_OK);
        }
        fs.writeFileSync(filepath, workbookBinary);
        console.log('Excel file saved successfully');
        return { success: true, data: filepath };
      } catch (writeError: any) {
        // 如果写入失败，尝试使用用户数据目录
        console.warn('在当前目录写入Excel文件失败，尝试使用用户数据目录:', writeError.message);
        if (!useUserDataDir) {
          try {
            const userDataPath = (process as any).userDataPath || require('electron').app.getPath('userData');
            const userReportsDir = path.join(userDataPath, 'reports');
            if (!fs.existsSync(userReportsDir)) {
              fs.mkdirSync(userReportsDir, { recursive: true });
            }
            const userFilepath = path.join(userReportsDir, filename);
            // 检查用户目录文件是否可写
            if (fs.existsSync(userFilepath)) {
              fs.accessSync(userFilepath, fs.constants.W_OK);
            }
            fs.writeFileSync(userFilepath, workbookBinary);
            console.log('Excel file saved successfully in user directory');
            return { success: true, data: userFilepath };
          } catch (userDirWriteError: any) {
            console.error('无法在用户数据目录写入Excel文件:', userDirWriteError.message);
            throw new Error('Excel导出文件失败，请检查文件权限或稍后再试');
          }
        } else {
          throw new Error('Excel导出文件失败，请检查文件权限或稍后再试');
        }
      }
    } catch (error: any) {
      console.error('导出Excel文件失败:', error);
      return { success: false, error: 'Excel导出文件失败，请检查文件权限或稍后再试' };
    }
  }

  /**
   * 导出数据到CSV文件
   * params: { siteName?: string, inputs?: object, results?: object, exportType?: 'all' | 'inputs' | 'results' }
   */
  static async exportToCsv(params: any): Promise<any> {
    try {
      const { 
        siteName = '计算数据', 
        inputs = {}, 
        results = {}, 
        exportType = 'all' 
      } = params;

      const dateStr = new Date().toISOString().replace(/[:.]/g, '-');
      // 使用与docxHelper.ts相同的路径处理方式
      let reportsDir = path.join(process.cwd(), 'reports');
      let useUserDataDir = false;
      
      try {
        if (!fs.existsSync(reportsDir)) {
          fs.mkdirSync(reportsDir, { recursive: true });
        }
        // 检查目录是否可写入
        fs.accessSync(reportsDir, fs.constants.W_OK);
      } catch (mkdirError: any) {
        // 如果创建目录失败或目录不可写，尝试使用用户数据目录
        console.warn('在当前目录创建reports文件夹失败，尝试使用用户数据目录:', mkdirError.message);
        try {
          const userDataPath = (process as any).userDataPath || require('electron').app.getPath('userData');
          const userReportsDir = path.join(userDataPath, 'reports');
          if (!fs.existsSync(userReportsDir)) {
            fs.mkdirSync(userReportsDir, { recursive: true });
          }
          // 检查用户目录是否可写入
          fs.accessSync(userReportsDir, fs.constants.W_OK);
          // 更新reportsDir为用户数据目录
          reportsDir = userReportsDir;
          useUserDataDir = true;
        } catch (userDirError: any) {
          console.error('无法在用户数据目录创建reports文件夹:', userDirError.message);
          throw new Error('导出文件失败，请检查文件权限或稍后再试');
        }
      }
      
      console.log('Reports directory path:', reportsDir);
      const exportedFiles = [];
      
      // 导出输入参数
      if (exportType === 'all' || exportType === 'inputs') {
        const inputData = this.prepareInputDataForCsv(inputs);
        const inputFilename = `${siteName.replace(/[^a-z0-9_\u4e00-\u9fa5]/gi, '_')}_输入参数_${dateStr}.csv`;
        const inputFilepath = path.join(reportsDir, inputFilename);
        
        console.log('Attempting to save CSV input file to:', inputFilepath);
        
        try {
          // 检查文件是否已存在且可写
          if (fs.existsSync(inputFilepath)) {
            fs.accessSync(inputFilepath, fs.constants.W_OK);
          }
          // 使用fs.writeFileSync直接写入CSV文件
          const inputCsvContent = this.convertToCsvFormat(inputData, ['参数', '数值', '单位']);
          fs.writeFileSync(inputFilepath, '\uFEFF' + inputCsvContent); // 添加BOM以支持中文
        } catch (writeError: any) {
          // 如果写入失败，尝试使用用户数据目录
          console.warn('在当前目录写入CSV输入文件失败，尝试使用用户数据目录:', writeError.message);
          if (!useUserDataDir) {
            try {
              const userDataPath = (process as any).userDataPath || require('electron').app.getPath('userData');
              const userReportsDir = path.join(userDataPath, 'reports');
              if (!fs.existsSync(userReportsDir)) {
                fs.mkdirSync(userReportsDir, { recursive: true });
              }
              const userFilepath = path.join(userReportsDir, inputFilename);
              // 检查用户目录文件是否可写
              if (fs.existsSync(userFilepath)) {
                fs.accessSync(userFilepath, fs.constants.W_OK);
              }
              const inputCsvContent = this.convertToCsvFormat(inputData, ['参数', '数值', '单位']);
              fs.writeFileSync(userFilepath, '\uFEFF' + inputCsvContent); // 添加BOM以支持中文
              exportedFiles.push({ type: 'inputs', path: userFilepath });
            } catch (userDirWriteError: any) {
              console.error('无法在用户数据目录写入CSV输入文件:', userDirWriteError.message);
              throw new Error('CSV导出文件失败，请检查文件权限或稍后再试');
            }
          } else {
            throw new Error('CSV导出文件失败，请检查文件权限或稍后再试');
          }
        }
        
        exportedFiles.push({ type: 'inputs', path: inputFilepath });
        console.log('CSV input file saved successfully');
      }
      
      // 导出计算结果
      if (exportType === 'all' || exportType === 'results') {
        const resultData = this.prepareResultDataForCsv(results);
        const resultFilename = `${siteName.replace(/[^a-z0-9_\u4e00-\u9fa5]/gi, '_')}_计算结果_${dateStr}.csv`;
        const resultFilepath = path.join(reportsDir, resultFilename);
        
        console.log('Attempting to save CSV result file to:', resultFilepath);
        
        try {
          // 检查文件是否已存在且可写
          if (fs.existsSync(resultFilepath)) {
            fs.accessSync(resultFilepath, fs.constants.W_OK);
          }
          // 使用fs.writeFileSync直接写入CSV文件
          const resultCsvContent = this.convertToCsvFormat(resultData, ['项目', '数值', '单位']);
          fs.writeFileSync(resultFilepath, '\uFEFF' + resultCsvContent); // 添加BOM以支持中文
        } catch (writeError: any) {
          // 如果写入失败，尝试使用用户数据目录
          console.warn('在当前目录写入CSV结果文件失败，尝试使用用户数据目录:', writeError.message);
          if (!useUserDataDir) {
            try {
              const userDataPath = (process as any).userDataPath || require('electron').app.getPath('userData');
              const userReportsDir = path.join(userDataPath, 'reports');
              if (!fs.existsSync(userReportsDir)) {
                fs.mkdirSync(userReportsDir, { recursive: true });
              }
              const userFilepath = path.join(userReportsDir, resultFilename);
              // 检查用户目录文件是否可写
              if (fs.existsSync(userFilepath)) {
                fs.accessSync(userFilepath, fs.constants.W_OK);
              }
              const resultCsvContent = this.convertToCsvFormat(resultData, ['项目', '数值', '单位']);
              fs.writeFileSync(userFilepath, '\uFEFF' + resultCsvContent); // 添加BOM以支持中文
              exportedFiles.push({ type: 'results', path: userFilepath });
            } catch (userDirWriteError: any) {
              console.error('无法在用户数据目录写入CSV结果文件:', userDirWriteError.message);
              throw new Error('CSV导出文件失败，请检查文件权限或稍后再试');
            }
          } else {
            throw new Error('CSV导出文件失败，请检查文件权限或稍后再试');
          }
        }
        
        exportedFiles.push({ type: 'results', path: resultFilepath });
        console.log('CSV result file saved successfully');
      }
      
      return { success: true, data: exportedFiles };
    } catch (error: any) {
      console.error('导出CSV文件失败:', error);
      return { success: false, error: 'CSV导出文件失败，请检查文件权限或稍后再试' };
    }
  }

  /**
   * 将数据转换为CSV格式字符串
   * @param data 数据数组
   * @param headers 表头
   * @returns CSV格式字符串
   */
  private static convertToCsvFormat(data: any[], headers: string[]): string {
    // 创建CSV内容
    let csvContent = '';
    
    // 添加表头
    csvContent += headers.join(',') + '\n';
    
    // 添加数据行
    data.forEach(row => {
      const values = headers.map(header => {
        const value = row[header] || '';
        // 转义包含逗号或引号的值
        if (typeof value === 'string' && (value.includes(',') || value.includes('"'))) {
          return `"${value.replace(/"/g, '""')}"`;
        }
        return value;
      });
      csvContent += values.join(',') + '\n';
    });
    
    return csvContent;
  }

  /**
   * 从Excel文件导入数据
   * params: { filePath: string }
   */
  static async importFromExcel(params: { filePath: string }): Promise<any> {
    try {
      const { filePath } = params;
      
      if (!fs.existsSync(filePath)) {
        throw new Error('文件不存在');
      }
      
      // 读取Excel文件
      const workbook = XLSX.readFile(filePath);
      const sheetNames = workbook.SheetNames;
      
      const importedData: any = {};
      
      // 处理每个工作表
      sheetNames.forEach(sheetName => {
        const worksheet = workbook.Sheets[sheetName];
        const data = XLSX.utils.sheet_to_json(worksheet);
        importedData[sheetName] = data;
      });
      
      return { success: true, data: importedData };
    } catch (error: any) {
      console.error('导入Excel文件失败:', error);
      return { success: false, error: error.message };
    }
  }

  /**
   * 从CSV文件导入数据
   * params: { filePath: string }
   */
  static async importFromCsv(params: { filePath: string }): Promise<any> {
    try {
      const { filePath } = params;
      
      if (!fs.existsSync(filePath)) {
        throw new Error('文件不存在');
      }
      
      return new Promise((resolve, reject) => {
        const results: any[] = [];
        
        fs.createReadStream(filePath)
          .pipe(require('csv-parser')())
          .on('data', (data: any) => results.push(data))
          .on('end', () => {
            resolve({ success: true, data: results });
          })
          .on('error', (error: any) => {
            reject({ success: false, error: error.message });
          });
      });
    } catch (error: any) {
      console.error('导入CSV文件失败:', error);
      return { success: false, error: error.message };
    }
  }

  /**
   * 准备Excel格式的输入参数数据
   */
  private static prepareInputDataForExcel(inputs: any): any[] {
    const data: any[] = [];
    
    Object.keys(inputs || {}).forEach((key) => {
      const val = inputs[key];
      let value = '';
      let unit = '';
      
      // 处理参数格式 {value, unit}
      if (val !== null && typeof val === 'object' && ('value' in val || 'unit' in val)) {
        value = val.value !== undefined ? String(val.value) : JSON.stringify(val);
        unit = val.unit || '';
      } else if (val !== null && typeof val === 'object') {
        value = JSON.stringify(val, null, 2);
      } else {
        value = String(val);
      }
      
      data.push({
        "参数": key,
        "数值": value,
        "单位": unit
      });
    });
    
    return data;
  }

  /**
   * 准备Excel格式的计算结果数据
   */
  private static prepareResultDataForExcel(results: any): any[] {
    const data: any[] = [];
    
    Object.keys(results || {}).forEach((key) => {
      const val = results[key];
      let value = '';
      let unit = '';
      
      // 处理参数格式 {value, unit}
      if (val !== null && typeof val === 'object' && ('value' in val || 'unit' in val)) {
        value = val.value !== undefined ? String(val.value) : JSON.stringify(val);
        unit = val.unit || '';
      } else if (val !== null && typeof val === 'object') {
        value = JSON.stringify(val, null, 2);
      } else {
        value = String(val);
      }
      
      data.push({
        "项目": key,
        "数值": value,
        "单位": unit
      });
    });
    
    return data;
  }

  /**
   * 准备CSV格式的输入参数数据
   */
  private static prepareInputDataForCsv(inputs: any): any[] {
    const data: any[] = [];
    
    Object.keys(inputs || {}).forEach((key) => {
      const val = inputs[key];
      let value = '';
      let unit = '';
      
      // 处理参数格式 {value, unit}
      if (val !== null && typeof val === 'object' && ('value' in val || 'unit' in val)) {
        value = val.value !== undefined ? String(val.value) : JSON.stringify(val);
        unit = val.unit || '';
      } else if (val !== null && typeof val === 'object') {
        value = JSON.stringify(val, null, 2);
      } else {
        value = String(val);
      }
      
      data.push({
        param: key,
        value: value,
        unit: unit
      });
    });
    
    return data;
  }

  /**
   * 准备CSV格式的计算结果数据
   */
  private static prepareResultDataForCsv(results: any): any[] {
    const data: any[] = [];
    
    Object.keys(results || {}).forEach((key) => {
      const val = results[key];
      let value = '';
      let unit = '';
      
      // 处理参数格式 {value, unit}
      if (val !== null && typeof val === 'object' && ('value' in val || 'unit' in val)) {
        value = val.value !== undefined ? String(val.value) : JSON.stringify(val);
        unit = val.unit || '';
      } else if (val !== null && typeof val === 'object') {
        value = JSON.stringify(val, null, 2);
      } else {
        value = String(val);
      }
      
      data.push({
        name: key,
        value: value,
        unit: unit
      });
    });
    
    return data;
  }
}